package com.zzx.dao.imql;

import com.zzx.Util.DruidUtil;
import com.zzx.bean.Express;
import com.zzx.dao.BaseExpressDao;
import com.zzx.exception.DuplicateCodeException;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: Spider Man
 * @Description:
 * @Date Created in 2020-09-12 16:00
 * @Modified By:
 */
public class ExpressDaoImp implements BaseExpressDao {
    public static final String SQL_CONSOLE = "SELECT COUNT(id) data1_size," +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_day," +
            "COUNT(STATUS=0 OR NULL) data2_size," +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND status=0 OR NULL) data2_day FROM EEXPRESS";
    public static final String SQL_FIND_ALL_EXPRESS_INFO = "SELECT * FROM EEXPRESS";
    public static final String SQL_FIND_ALL_EXPRESS_INFO_LIMIT = "SELECT * FROM EEXPRESS LIMIT ?,?";
    public static final String SQL_FIND_EXPRESS_BY_NUMBER = "SELECT * FROM EEXPRESS WHERE NUMBER=?";
    public static final String SQL_FIND_EXPRESS_BY_CODE = "SELECT * FROM EEXPRESS WHERE CODE=?";
    public static final String SQL_FIND_EXPRESS_BY_USER_PHONE = "SELECT * FROM EEXPRESS WHERE userPhone=?";
    public static final String SQL_FIND_EXPRESS_BY_USER_PHONE_AND_STATUS = "SELECT * FROM EEXPRESS WHERE userPhone=? AND status=?";
    public static final String SQL_FIND_EXPRESS_BY_SYS_PHONE = "SELECT * FROM EEXPRESS WHERE sysPhone=?";
    public static final String SQL_INSERT = "INSERT INTO EEXPRESS (number,username,userPhone,company,code,inTime,status,sysPhone) VALUES(?,?,?,?,?,NOW(),0,?)";
    public static final String SQL_UPDATE = "UPDATE EEXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?";
    public static final String SQL_DELETE = "DELETE FROM EEXPRESS WHERE ID=?";
    public static final String SQL_UPDATE_STATUS = "UPDATE EEXPRESS SET STATUS=1,OUTTIME=NOW(),CODE=NULL WHERE CODE=?";

    /**
     * 用于查询数据库中的全部快递（总数+新增），代取件快递（总数+新增）
     *
     * @return [{size:总数,day:新增},{size:总数,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String, Integer>> list = new ArrayList<>();
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            // 3.参数填充
            // 4.执行预编译的语句
            resultSet = preparedStatement.executeQuery();
            // 5.获取结果
            if (resultSet.next()) {
                int data1_size = resultSet.getInt("data1_size");
                int data1_day = resultSet.getInt("data1_day");
                int data2_size = resultSet.getInt("data2_size");
                int data2_day = resultSet.getInt("data2_day");
                Map map = new HashMap();
                map.put("data1_size", data1_size);
                map.put("data1_day", data1_day);
                Map map2 = new HashMap();
                map2.put("data2_size", data2_size);
                map2.put("data2_day", data2_day);
                list.add(map);
                list.add(map2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 用于查询所有快递的信息
     *
     * @param limit      是否分页的标记，true表示要分页，false表示不用分页
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 快递对象的集合
     */
    @Override
    public List<Express> findAllExpressInfo(boolean limit, int offset, int pageNumber) {
        ArrayList<Express> list = new ArrayList<>();
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            if (limit) {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL_EXPRESS_INFO_LIMIT);
                // 3.参数填充
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, pageNumber);
            } else {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL_EXPRESS_INFO);
            }
            // 4.执行预编译的语句
            resultSet = preparedStatement.executeQuery();
            // 5.获取结果
            while (resultSet.next()) {
                Integer id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String username = resultSet.getString("username");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                Integer status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express express = new Express(id, number, username, userPhone, company, code, inTime, outTime, status, sysPhone);
                list.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 根据快递单号查询快递信息
     *
     * @param number 快递单号
     * @return 查询的快递信息，单号不存在时返回null
     */
    @Override
    public Express findExpressByNumber(String number) {
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_FIND_EXPRESS_BY_NUMBER);
            // 3.参数填充
            preparedStatement.setString(1, number);
            // 4.执行预编译的语句
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                // 5.获取结果
                Integer id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                Integer status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express express = new Express(id, number, username, userPhone, company, code, inTime, outTime, status, sysPhone);
                return express;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 根据取件码查询快递信息
     *
     * @param code 取件码
     * @return 查询的快递信息，取件码不存在时返回null
     */
    @Override
    public Express findExpressByCode(String code) {
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_FIND_EXPRESS_BY_CODE);
            // 3.参数填充
            preparedStatement.setString(1, code);
            // 4.执行预编译的语句
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                // 5.获取结果
                Integer id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userPhone = resultSet.getString("userPhone");
                String company = resultSet.getString("company");
                String number = resultSet.getString("number");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                Integer status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express express = new Express(id, number, username, userPhone, company, code, inTime, outTime, status, sysPhone);
                return express;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 根据用户手机号码查询快递信息
     *
     * @param userPhone 用户手机号码
     * @return 查询的快递信息，用户手机号码不存在时返回null
     */
    @Override
    public List<Express> findExpressByUserPhone(String userPhone) {
        ArrayList<Express> list = new ArrayList<>();
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_FIND_EXPRESS_BY_USER_PHONE);
            // 3.参数填充
            preparedStatement.setString(1, userPhone);
            // 4.执行预编译的语句
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                // 5.获取结果
                Integer id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String code = resultSet.getString("code");
                String company = resultSet.getString("company");
                String number = resultSet.getString("number");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                Integer status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysPhone");
                Express express = new Express(id, number, username, userPhone, company, code, inTime, outTime, status, sysPhone);
                list.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 根据用户手机号码和快递的状态码查询快递信息
     *
     * @param userPhone 用户手机号码
     * @return 查询的快递信息，用户手机号码不存在时返回null
     */
    @Override
    public List<Express> findExpressByUserPhoneAndStatus(String userPhone,int status) {
        ArrayList<Express> list = new ArrayList<>();
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_FIND_EXPRESS_BY_USER_PHONE_AND_STATUS);
            // 3.参数填充
            preparedStatement.setString(1, userPhone);
            preparedStatement.setInt(2, status);
            // 4.执行预编译的语句
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                // 5.获取结果
                Integer id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String code = resultSet.getString("code");
                String company = resultSet.getString("company");
                String number = resultSet.getString("number");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                String sysPhone = resultSet.getString("sysPhone");
                Express express = new Express(id, number, username, userPhone, company, code, inTime, outTime,status, sysPhone);
                list.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 根据录入人手机号码查询快递信息
     *
     * @param sysPhone 录入人手机号码
     * @return 查询的快递信息，录入人手机号码不存在时返回null
     */
    @Override
    public List<Express> findExpressBySysPhone(String sysPhone) {
        ArrayList<Express> list = new ArrayList<>();
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_FIND_EXPRESS_BY_SYS_PHONE);
            // 3.参数填充
            preparedStatement.setString(1, sysPhone);
            // 4.执行预编译的语句
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                // 5.获取结果
                Integer id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String code = resultSet.getString("code");
                String company = resultSet.getString("company");
                String number = resultSet.getString("number");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                Timestamp outTime = resultSet.getTimestamp("outTime");
                Integer status = resultSet.getInt("status");
                String userPhone = resultSet.getString("userPhone");
                Express express = new Express(id, number, username, userPhone, company, code, inTime, outTime, status, sysPhone);
                list.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 快递的录入
     *
     * @param express 快递对象
     * @return ture表示录入成功，false表示失败
     */
    @Override
    public boolean insert(Express express) throws DuplicateCodeException {
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            // 3.参数填充
            preparedStatement.setString(1, express.getNumber());
            preparedStatement.setString(2, express.getUsername());
            preparedStatement.setString(3, express.getUserPhone());
            preparedStatement.setString(4, express.getCompany());
            preparedStatement.setString(5, express.getCode());
            preparedStatement.setString(6, express.getSysPhone());
            // 4.执行预编译的语句
            // 5.获取结果
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            if (throwables.getMessage().endsWith("for key 'number'")) {
                throw new DuplicateCodeException();
            } else {
                throwables.printStackTrace();
            }
        } finally {
            // 6.释放资源
            DruidUtil.closeConnectionAndStatement(connection, preparedStatement);
        }
        return false;
    }

    /**
     * 快递的修改
     *
     * @param id      快递的ID
     * @param express 新的快递对象
     * @return ture表示更新成功，false表示更新失败
     */
    @Override
    public boolean update(int id, Express express) {
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            // 3.参数填充
            preparedStatement.setString(1, express.getNumber());
            preparedStatement.setString(2, express.getUsername());
            preparedStatement.setString(3, express.getCompany());
            preparedStatement.setInt(4, express.getStatus());
            preparedStatement.setInt(5, id);
            // 4.执行预编译的语句
            // 5.获取结果
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.closeConnectionAndStatement(connection, preparedStatement);
        }
        return false;
    }

    /**
     * 更加ID删除快递信息
     *
     * @param id 要删除的快递ID
     * @return true表示成功，false表示删除失败
     */
    @Override
    public boolean delete(int id) {
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            // 3.参数填充
            preparedStatement.setInt(1,id);
            // 4.执行预编译的语句
            // 5.获取结果
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.closeConnectionAndStatement(connection, preparedStatement);
        }
        return false;
    }

    /**
     * 更改快递的状态
     *
     * @param code 快递单号
     * @return true表示成功，false表示失败
     */
    @Override
    public boolean updateStatus(String code) {
        // 1.获取连接
        Connection connection = DruidUtil.getConnection();
        // 2.预编译sql语句
        PreparedStatement preparedStatement = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_UPDATE_STATUS);
            // 3.参数填充
            preparedStatement.setString(1, code);
            // 4.执行预编译的语句
            // 5.获取结果
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            DruidUtil.closeConnectionAndStatement(connection, preparedStatement);
        }
        return false;
    }
}
